<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Tongjinet extends Common
{
    public function getnet($nid){
        $arr=db("cg_net")->where(["id"=>$nid])->find();
        return json_encode($arr);
    }
    //决策依据查询
    public function jccx(){
        $fenqu=db::table("cg_market_zoning")->where("isdel=0")->field("id,title")->select();
        $this->assign("fenqu",$fenqu);
        return $this->fetch();
    }
    //网点情况
    public function netyj($nid){
       
        $net=Db::table("cg_net")->where(["id"=>$nid])->find();
        $this->assign("net",$net);
        //条码情况
        $tm=Db::Table("cg_net_barcode")->alias("b")->join("pro_products p","b.prod=p.id")->where(["b.net_id"=>$net["id"],"tuichang"=>0])
        ->field("p.pro_no,p.pro_name,b.intime,b.infee,b.retailPrice")->select();
        for($i=0;$i<count($tm);$i++){
            $tm[$i]["intime"]=date("Y-m-d",$tm[$i]["intime"]);
        }
        $this->assign("tm",$tm);
        $ctm=count($tm);
        $this->assign("ctm",$ctm);
        //退场条码
        $tuim=Db::Table("cg_net_barcode")->alias("b")->join("pro_products p","b.prod=p.id")->where(["b.net_id"=>$net["id"],"tuichang"=>1])
        ->field("p.pro_no,p.pro_name,b.tuichangshijian,b.infee,b.retailPrice,b.tuichangyuanyin")->select();
        for($i=0;$i<count($tuim);$i++){
            $tuim[$i]["tuichangshijian"]=date("Y-m-d",$tuim[$i]["tuichangshijian"]);
        }
        $this->assign("tuim",$tuim);
        $ctuim=count($tuim);
        $this->assign("ctuim",$ctuim);
        //竞品情况
        $jp=Db::table("cg_net_competitor")->alias("n")->join("competitor c","n.name=c.id")->join("competitor_category cc","c.category_id=cc.id")
        ->where(["n.isdel"=>0,"n.net_id"=>$net["id"],"status"=>0])
        ->field("cc.category_name,c.c_name,c.format,n.price,n.brief")->select();
        $this->assign("jp",$jp);
        $cjp=count($jp);
        $this->assign("cjp",$cjp);
        //导购情况
        $dg=Db::table("cg_net_dg")->alias("d")->join("employee e","d.dg_id=e.id")->where("d.net_id={$net["id"]}")
        ->field("e.em_name,e.gender,e.mobile,e.state,e.entry")->select();
        $this->assign("dg",$dg);
        $cdg=count($dg);
        $this->assign("cdg",$cdg);
        //最近5次活动
        $hd=Db::table("cg_ti")->alias("t")
        ->join("cg_ti_list td","t.id=td.ti_id")
        ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1)")
        ->order("td.start desc")->limit(0,5)
        ->field("t.sn,td.sn as tdsn,t.type_id,t.cuser,td.feetype,td.pizhunhanshui,td.fb_shijitourufeiyong,td.totalExpectedSales,td.fb_shijixiaoliang,td.start,td.end,td.product")
        ->select();
        for($i=0;$i<count($hd);$i++){
            $hd[$i]["cuser"]=Db::Table("employee")->where(["id"=>$hd[$i]["cuser"]])->value("em_name");
            if($hd[$i]["product"])
                $hd[$i]["product"]=Db::table("pro_products")->where(["id"=>$hd[$i]["product"]])->value("pro_name");
            $hd[$i]["start"]=date("Y-m-d",$hd[$i]["start"]);
            $hd[$i]["end"]=date("Y-m-d",$hd[$i]["end"]);
            $hd[$i]["feetype"]=Db::table("cg_feiyongkemu3")->where(["id"=>$hd[$i]["feetype"]])->value("title");
        }
        $this->assign("hd",$hd);
        $chd=Db::table("cg_ti")->alias("t")
        ->join("cg_ti_list td","t.id=td.ti_id")
        ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1)")->count();
        if(!$chd)
            $chd=0;
        $this->assign("chd",$chd);
        //网点拜访情况
        $bf=Db::table("yd_wdbf")->alias("bf")->join("employee e","bf.bfr=e.id")
        ->join("department_employee de","de.employee_id=e.id")->join("department d","d.id=de.department_id")
        ->where(["bf.wdid"=>$net["id"],"de.index"=>1])->order("dzsj desc")
        ->field("e.em_name,d.de_name,bf.ddsj,bf.dzsj,bf.rq")->select();
        $this->assign("bf",$bf);
        $cbf=count($bf);
        $this->assign("cbf",$cbf);
        //决策依据1
        $startday=strtotime("2017-01-01");
        $cainian=Db::table("cg_cainian")->where("starttime>=$startday")->select();
        $hasc=[];
        for($i=0;$i<count($cainian);$i++){
            $sum=Db::table("cg_ti")->alias("t")
            ->join("cg_ti_list td","t.id=td.ti_id")
            ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$cainian[$i]["starttime"]} and td.start<={$cainian[$i]["endtime"]} and td.net_id={$net["id"]}")->sum("yjsjtrfy");
            $st=date("Y-m-d",$cainian[$i]["starttime"]);
            $ed=date("Y-m-d",$cainian[$i]["endtime"]);
            $sumfy=Db::table("bx_fy2")->where("wd_id={$net["id"]} and fy2Date<='$ed' and fy2Date>='$st'")->sum("money");
            if($sum||$sumfy)
                $hasc[]=$cainian[$i];
        }
        for($i=0;$i<count($hasc);$i++){
            $hasc[$i]["kaohe2"]=Db::table("cg_cainian")->alias("c")->join("cg_kaoheqi1 k1","k1.cid=c.id")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where(["c.id"=>$hasc[$i]["id"]])
            ->field("k2.id,k2.title,k2.starttime,k2.endtime")->order("k2.starttime")->select();
            $hasc[$i]["zongzc"]=0;
            $hasc[$i]["zongfy"]=0;
            $hasc[$i]["zongxiaoliang"]=0;
            for($j=0;$j<count($hasc[$i]["kaohe2"]);$j++){
                $hasc[$i]["kaohe2"][$j]["zc"]='';
                $sumks=Db::table("cg_ti")->alias("t")
                ->join("cg_ti_list td","t.id=td.ti_id")
                ->where("t.status in (7,9) and t.status2 is null and (td.isOrNo is null or td.isOrNo=1) and td.start>={$hasc[$i]["kaohe2"][$j]["starttime"]} and td.start<={$hasc[$i]["kaohe2"][$j]["endtime"]} and td.net_id={$net["id"]}")->field("pizhunhanshui,fb_shijitourufeiyong,fb_shijixiaoliang")->select();
                //print_r($sumks);echo "<br>";
                $sumk=0;$xiaoliang=0;
                for($k=0;$k<count($sumks);$k++){
                    if($sumks[$k]["fb_shijitourufeiyong"])
                        $sumk+=$sumks[$k]["fb_shijitourufeiyong"];
                    else
                        $sumk+=$sumks[$k]["pizhunhanshui"];
                    if($sumks[$k]["fb_shijixiaoliang"]){
                        $xiaoliang+=$sumks[$k]["fb_shijixiaoliang"];
                    }

                }

                if($sumk){
                    $hasc[$i]["kaohe2"][$j]["zc"]=$sumk;
                    $hasc[$i]["zongzc"]+=$sumk;
                }
                if($xiaoliang){
                    $hasc[$i]["zongxiaoliang"]+=$xiaoliang;
                    $hasc[$i]["kaohe2"][$j]["xiaoliang"]=$xiaoliang;
                    $hasc[$i]["kaohe2"][$j]["fl"]=round($hasc[$i]["kaohe2"][$j]["zc"]/$hasc[$i]["kaohe2"][$j]["xiaoliang"]*100,2)."%";
                }else{
                    $hasc[$i]["kaohe2"][$j]["fl"]="-";
                    $hasc[$i]["kaohe2"][$j]["xiaoliang"]='-';
                }
                $hasc[$i]["kaohe2"][$j]["fy"]='';
                $st2=date("Y-m-d",$hasc[$i]["kaohe2"][$j]["starttime"]);
                $ed2=date("Y-m-d",$hasc[$i]["kaohe2"][$j]["endtime"]);
                $sumy=Db::table("bx_fy2")->where("wd_id={$net["id"]} and fy2Date<='$ed2' and fy2Date>='$st2'")->sum("money");
                if($sumy){
                    $hasc[$i]["kaohe2"][$j]["fy"]=$sumy;
                    $hasc[$i]["zongfy"]+=$sumy;
                }
            }
            $hasc[$i]["avgzc"]=$hasc[$i]["zongzc"]/12;
            $hasc[$i]["avgfy"]=$hasc[$i]["zongfy"]/12;
            if($hasc[$i]["zongxiaoliang"]){
                $hasc[$i]["fl"]=round($hasc[$i]["zongzc"]/$hasc[$i]["zongxiaoliang"]*100,2)."%";
            }else{
                $hasc[$i]["fl"]='-';
            }
        }
        $this->assign("hasc",$hasc);
        return $this->fetch();
    }
    //导购工资
    public function netdggz(){
        return $this->fetch();
    }
    //网点情况总览
    public function netqk(){
        $sarr=db("cg_market_provice p")->join("cg_market_zoning z","p.zoning=z.id")->where("p.isdel=0 and z.isdel=0")->field("p.id,p.title as sheng,z.title as fenqu")->select();
        for($i=0;$i<count($sarr);$i++){
            $dg=db("cg_jxs j")->join("cg_system s","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("n.isdel=0 and j.provice={$sarr[$i]["id"]} and n.guide=1")->count();
            $sarr[$i]["dg"]=0;
            if($dg){
                $sarr[$i]["dg"]=$dg;
            }
            $fdg=db("cg_jxs j")->join("cg_system s","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("n.isdel=0 and j.provice={$sarr[$i]["id"]} and n.guide=0")->count();
            $sarr[$i]["fdg"]=0;
            if($fdg){
                $sarr[$i]["fdg"]=$fdg;
            }
            $sarr[$i]["zong"]=$sarr[$i]["dg"]+$sarr[$i]["fdg"];
        }
        $this->assign("sarr",$sarr);
        return $this->fetch();
    }
    public function netqkdc(){
        $sarr=db("cg_market_provice p")->join("cg_market_zoning z","p.zoning=z.id")->where("p.isdel=0 and z.isdel=0")->field("p.id,p.title as sheng,z.title as fenqu")->select();
        $alldg=0;$allfdg=0;$allzong=0;
        for($i=0;$i<count($sarr);$i++){
            $dg=db("cg_jxs j")->join("cg_system s","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("n.isdel=0 and j.provice={$sarr[$i]["id"]} and n.guide=1")->count();
            $sarr[$i]["dg"]=0;
            if($dg){
                $sarr[$i]["dg"]=$dg;
                $alldg+=$dg;
            }
            $fdg=db("cg_jxs j")->join("cg_system s","s.jxs=j.id")->join("cg_net n","n.system_id=s.id")->where("n.isdel=0 and j.provice={$sarr[$i]["id"]} and n.guide=0")->count();
            $sarr[$i]["fdg"]=0;
            if($fdg){
                $sarr[$i]["fdg"]=$fdg;
                $allfdg+=$fdg;
            }
            $sarr[$i]["zong"]=$sarr[$i]["dg"]+$sarr[$i]["fdg"];
            $allzong+=$sarr[$i]["zong"];
        }
        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("网点情况总览");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "网点情况总览");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:D2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"导购网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"非导购网点数");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"总数");
         $h=3;
        for($i=0;$i<count($sarr);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$sarr[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$sarr[$i]["dg"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$sarr[$i]["fdg"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$sarr[$i]["zong"],PHPExcel_Cell_DataType::TYPE_STRING);
            
        }
        $h++;
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$alldg,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$allfdg,PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$allzong,PHPExcel_Cell_DataType::TYPE_STRING);
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //网点情况明细
    public function netqkmx($page=1,$list=10){
        $request = Request::instance();
        $data=$request->param();
        $where="n.isdel=0 ";
        if($data){
            if(trim($data["fenqu"])){
                $fenqu=trim($data["fenqu"]);
                $where.="and z.title='{$fenqu}' ";
            }
            if(trim($data["sheng"])){
                $sheng=trim($data["sheng"]);
                $where.="and p.title='{$sheng}' ";
            }
            if(trim($data["shi"])){
                $shi=trim($data["shi"]);
                $where.="and c.title='{$shi}' ";
            }
            
            if(trim($data["jxssn"])){
                $sn=trim($data["jxssn"]);
                $where.="and j.sn='{$sn}' ";
            }
            if(trim($data["jxstitle"])){
                $title=trim($data["jxstitle"]);
                $where.="and j.title='{$title}' ";
            }
            if(trim($data["systitle"])){
                $stitle=trim($data["systitle"]);
                $where.="and s.title='{$stitle}' ";
            }
            if(trim($data["netsn"])){
                $nsn=trim($data["netsn"]);
                $where.="and n.sn='{$nsn}' ";
            }
            if(trim($data["nettitle"])){
                $ntitle=trim($data["nettitle"]);
                $where.="and n.title='{$ntitle}' ";
            }
            if($data["guide"]){
                $dg=$data["guide"]-1;
                $where.="and n.guide=$dg ";
            }
        }else{
            $data=["fenqu"=>"","sheng"=>"","shi"=>"","jxssn"=>"","jxstitle"=>"","systitle"=>"","netsn"=>"","nettitle"=>"","guide"=>0];
        }
        $sum=db("cg_net n")->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")
        ->join("cg_market_city c","j.city=c.id")->where($where)->count();
         $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=db("cg_net n")->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
        ->join("cg_market_zoning z","j.zoning=z.id")->join("cg_market_provice p","j.provice=p.id")
        ->join("cg_market_city c","j.city=c.id")->join("employee ez","ez.id=z.head","LEFT")->join("employee ep","ep.id=p.head","LEFT")
        ->join("employee ec","ec.id=c.head","LEFT")->where($where)
        ->order("n.id")->limit($start,$list)
        ->field("z.title as fenqu,p.title as sheng,c.title as shi,ez.em_name as fenquhead,ep.em_name as shenghead,ec.em_name as shihead,n.id,n.title as net,n.sn as netsn,s.title as sys,j.title as jxs,n.stuff")
        ->select();
        for ($i=0; $i < count($apply); $i++) { 
            # code...
              $stuff=unserialize($apply[$i]['stuff']);
            //print_r($stuff);
            $apply[$i]["yewu"]=[];
            if($stuff){
                foreach ($stuff as $k => $v) {
                    # code...
                    $apply[$i]["yewu"][]=db("employee")->where("id={$v}")->value("em_name");
                }
            }
        }
         $this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        return $this->fetch();
    }
    //网点发运排名
    public function netfy($cid='',$tid=5){
        $this->assign("tid",$tid);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,5)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 2:
                # code...
                  $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,25)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 3:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,50)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 4:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney)")->limit(0,50)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 5:
                # code...
                  $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            
        }
         $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("n.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }
       $this->assign("zong",$zong);
       $this->assign("apply",$apply);
       $this->assign("kaohe2",$kaohe2);
        return $this->fetch();
    }
    public function netfydc($cid,$tid){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); $where="1=1 ";
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }
        switch ($tid) {
            case 1:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,5)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 2:
                # code...
                  $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,25)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 3:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->limit(0,50)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 4:
                # code...
                 $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney)")->limit(0,50)->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            case 5:
                # code...
                  $apply=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")
                ->join("cg_market_zoning z","p.zoning=z.id")->join("cg_market_city c","j.city=c.id")
                ->where("f.fyDate>='{$cst}' and f.fyDate<='{$ced}' and $where")
                ->group("n.id")->order("sum(fyZheMoney) desc")->field("n.id,j.title as jxs,s.title as sys,n.title as net,c.title as shi,p.title as sheng,sum(fyZheMoney) as zong,z.title as fenqu")->select();
                break;
            
        }
         $zong=0;
       for($i=0;$i<count($apply);$i++){
            for($j=0;$j<count($kaohe2);$j++){
                $st=date("Y-m-d",$kaohe2[$j]["starttime"]);
                $ed=date("Y-m-d",$kaohe2[$j]["endtime"]);
                $jine=db("bx_fy f")->join("cg_net n","f.sdNet_id=n.id")->join("cg_system s","n.system_id=s.id")
                 ->join("cg_jxs j","s.jxs=j.id")->join("cg_market_provice p","j.provice=p.id")->join("cg_market_zoning z","p.zoning=z.id")
                ->where("n.id={$apply[$i]["id"]} and f.fyDate>='{$st}' and f.fyDate<='{$ed}'")->sum("fyZheMoney");
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                }else{
                    $apply[$i]["jine"][$j]=0;
                }
                if(isset($kaohe2[$j]["zjine"]))
                    $kaohe2[$j]["zjine"]+=$apply[$i]["jine"][$j];
                else
                    $kaohe2[$j]["zjine"]=$apply[$i]["jine"][$j];
            }
            $zong+=$apply[$i]["zong"];
       }

        $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("网点发运排名");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:T1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "网点发运排名");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:T2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"网点名");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"系统名");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"经销商名");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"区域");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('S3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('T3',"小计");
         $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sys"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$apply[$i]["zong"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
         $h++;
         $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':G'.$h);
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$kaohe2[0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$kaohe2[1]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$kaohe2[2]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$kaohe2[3]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$kaohe2[4]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$kaohe2[5]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$kaohe2[6]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$kaohe2[7]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$kaohe2[8]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$kaohe2[9]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$kaohe2[10]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$kaohe2[11]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);
        $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //网点费用投入情况
    public function nettr($cid='',$tid=5){
         $this->assign("tid",$tid);
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
        $this->assign("nian",$nian);
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); 
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }

        switch ($tid) {
            case 1:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,5)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 2:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,20)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 3:
                # code...
                 $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,50)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 4:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong)")->limit(0,50)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
             case 5:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            default:
                # code...
                break;
        }
        $zong=0;
        for ($i=0; $i < count($apply); $i++) { 
            # code...
            $apply[$i]["fenqu"]=db("cg_market_zoning")->where(["id"=>$apply[$i]["zoning"]])->value("title");
            $apply[$i]["sheng"]=db("cg_market_provice")->where(["id"=>$apply[$i]["provice"]])->value("title");
            $apply[$i]["shi"]=db("cg_market_city")->where(["id"=>$apply[$i]["city"]])->value("title");
            for($j=0;$j<count($kaohe2);$j++){
                $jine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and td.net_id={$apply[$i]["id"]}")->sum("td.fb_shijitourufeiyong");

                $apply[$i]["jine"][$j]=0;
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                    $kaohe2[$j]["zjine"]+=round($jine,2);
                    $zong+=round($jine,2);
                }
            }
        }
        $this->assign("kaohe2",$kaohe2);
        $this->assign("apply",$apply);
        $this->assign("zong",$zong);
        return $this->fetch();
    }
    public function nettrdc($cid,$tid){
         
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $nian=db("cg_cainian")->where(["id"=>$cid])->value("title");
      
        $cn=db("cg_cainian")->where(["id"=>$cid])->find();
        $cst=date("Y-m-d",$cn["starttime"]);
        $ced=date("Y-m-d",$cn["endtime"]); 
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->order("k2.starttime")->where("k1.cid=$cid")->field("k2.id,k2.title,k2.starttime,k2.endtime,k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end")->select();
        foreach ($kaohe2 as $k => $v) {
            # code...
            $kaohe2[$k]["zjine"]=0;
        }

        switch ($tid) {
            case 1:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,5)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 2:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,20)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 3:
                # code...
                 $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")->limit(0,50)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            case 4:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong)")->limit(0,50)
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
             case 5:
                # code...
                $apply=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_net n","td.net_id=n.id")
                ->join("cg_system s","n.system_id=s.id")->join("cg_jxs j","s.jxs=j.id")
                ->where("t.status=7 and (t.status2=0 or t.status2 is null) and td.start>={$cn["starttime"]} and td.start<={$cn["endtime"]}")
                ->group("td.net_id")->order("sum(fb_shijitourufeiyong) desc")
                ->field("n.id,n.title as net,s.title as sys,j.title as jxs,j.id as jid,sum(fb_shijitourufeiyong) as zjine,j.zoning,j.provice,j.city")->select();
                break;
            default:
                # code...
                break;
        }
        $zong=0;
        for ($i=0; $i < count($apply); $i++) { 
            # code...
            $apply[$i]["fenqu"]=db("cg_market_zoning")->where(["id"=>$apply[$i]["zoning"]])->value("title");
            $apply[$i]["sheng"]=db("cg_market_provice")->where(["id"=>$apply[$i]["provice"]])->value("title");
            $apply[$i]["shi"]=db("cg_market_city")->where(["id"=>$apply[$i]["city"]])->value("title");
            for($j=0;$j<count($kaohe2);$j++){
                $jine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")
                    ->where("t.status=7 and (t.status2 is null or t.status2=0) and td.start>={$kaohe2[$j]["starttime"]} and td.start<={$kaohe2[$j]["endtime"]} and td.net_id={$apply[$i]["id"]}")->sum("td.fb_shijitourufeiyong");

                $apply[$i]["jine"][$j]=0;
                if($jine){
                    $apply[$i]["jine"][$j]=round($jine,2);
                    $kaohe2[$j]["zjine"]+=round($jine,2);
                    $zong+=round($jine,2);
                }
            }
        }
            $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("{$nian}年网点费用投入情况");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:T1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "{$nian}年网点费用投入情况");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:T2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:T3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"分区");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"省份");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"城市");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"经销商");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"系统");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"网点");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('S3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('T3',"小计");
        $h=3;
        for($i=0;$i<count($apply);$i++){
            $h++;
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$apply[$i]["fenqu"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$apply[$i]["sheng"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$apply[$i]["shi"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$apply[$i]["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$apply[$i]["sys"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$apply[$i]["net"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$apply[$i]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$apply[$i]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$apply[$i]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$apply[$i]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$apply[$i]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$apply[$i]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$apply[$i]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$apply[$i]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$apply[$i]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('Q'.$h,$apply[$i]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('R'.$h,$apply[$i]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('S'.$h,$apply[$i]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$apply[$i]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $h++;
        $objPHPExcel->getActiveSheet()->mergeCells("A{$h}:S{$h}");
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,"总计",PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('T'.$h,$zong,PHPExcel_Cell_DataType::TYPE_STRING);
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:T'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
             $objPHPExcel->getActiveSheet()->getColumnDimension('T')->setWidth(20);
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    //网点营销费用报表
    public function netyx($cid=''){
        $cainian=Db::table("cg_cainian")->order("starttime")->select();
        if(!$cid){
            $now=time();
            $cid=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
        }
        $this->assign("cainian",$cainian);
        $this->assign("cid",$cid);
        $fenqu=db("cg_market_zoning")->where("isdel=0")->select();
        $this->assign("fenqu",$fenqu);

        $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
        $this->assign("kemu",$kemu);
        return $this->fetch();
    }
    public function query(){
        $request = Request::instance();
        $data = $request->param();
       //print_r($data);
        $cainian=db("cg_cainian")->where(["id"=>$data["cid"]])->find();
        $this->assign("cid",$data["cid"]);
        $this->assign("cainian",$cainian);
        $kaohe1=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$data["cid"]}")->order("k2.starttime")->field("k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $this->assign("kaohe1",$kaohe1);
        $pid=$data["system"];
        $farr=db("cg_net")->where("isdel=0 and system_id=$pid")->field("id,title")->select();
        if($data["fx_kind"]==2){
            $kid=implode(',',$data["kemu"]);
            $kidstr=implode('-',$data["kemu"]);
            $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        }else{
            $kemu=db("cg_feiyongkemu3")->where("esuseway like '%\"1\"%' or esuseway like '%\"2\"%' or esuseway like '%\"3\"%' ")->select();
            $karr=[];
            foreach ($kemu as $key => $value) {
                # code...
                $karr[]=$value["id"];
            }
            $kidstr=implode('-',$karr);
        }
        $this->assign("kid",$kidstr);
        $this->assign("pid",$pid);
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0,0,0,0,0,0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe1);$k++){
                    $start=$kaohe1[$k]["starttime"];
                    $end=$kaohe1[$k]["endtime"]+3600*24;
                    $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                ->join("cg_net n","td.net_id=n.id")->join("cg_system s","n.system_id=s.id")
                    ->where($where."and n.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
        $this->assign("farr",$farr);
        return $this->fetch();
    }
    public function daochuqu($pid,$cid,$kid){
        $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
        $kaohe1=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$cid}")->order("k2.starttime")->field("k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $farr=db("cg_net")->where("isdel=0 and system_id=$pid")->field("id,title")->select();
        $kid=implode(',',explode('-',$kid));
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        for($i=0;$i<count($farr);$i++){
            $farr[$i]["ckemu"]=count($kemu);
            $farr[$i]["kemu"]=[];
            for($j=0;$j<count($kemu);$j++){
                $farr[$i]["kemu"][$j]["title"]=$kemu[$j]["title"];
                $farr[$i]["kemu"][$j]["jine"]=[0,0,0,0,0,0,0,0,0,0,0,0];
                $farr[$i]["kemu"][$j]["zjine"]=0;
                for($k=0;$k<count($kaohe1);$k++){
                    $start=$kaohe1[$k]["starttime"];
                    $end=$kaohe1[$k]["endtime"]+3600*24;
                    $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                ->join("cg_net n","td.net_id=n.id")->join("cg_system s","n.system_id=s.id")
                    ->where($where."and n.id={$farr[$i]["id"]} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                    foreach ($tmpjine as $key => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["fb_shijitourufeiyong"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["fb_shijitourufeiyong"];
                        }else{
                            $farr[$i]["kemu"][$j]["jine"][$k]+=$v["yjsjtrfy"];
                            $farr[$i]["kemu"][$j]["zjine"]+=$v["yjsjtrfy"];
                        }
                    }
                }
                $farr[$i]["kemu"][$j]["avg"]=round($farr[$i]["kemu"][$j]["zjine"]/4,2);
            }
        }
         $eid = Session::get("employee_id");
        $users = Db::table("employee")->where(["id" => $eid])->find();

        Vendor("PHPExcel");


        Vendor("PHPExcel.Writer.Excel5");

        // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel = new PHPExcel();


        $objProps = $objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("网点营销费用报表");

        #################总标题

        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:P1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', "网点营销费用报表");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);
        #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:P2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "(本表单由软件导出，导出时间：" . date("Y-m-d H:i:s") . "；导出人：{$users["em_name"]})");
        //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(12);
         $objPHPExcel->getActiveSheet()->getStyle('3')->getAlignment()->setWrapText(true);
        
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
             //水平剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
         $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"网点名");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"1月");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"2月");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"3月");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"4月");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"5月");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"6月");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"7月");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"8月");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"9月");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"10月");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"11月");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"12月");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"平均");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"小计");
        $h=3;
        for($i=0;$i<count($farr);$i++){
            $h++;$endh=$h+$farr[$i]["ckemu"]-1;
            if($farr[$i]["ckemu"]>1){
                $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':A'.$endh);
            }
            
             //垂直剧中
            $objPHPExcel->getActiveSheet()->getStyle('A'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('A'.$h,$farr[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][0]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][0]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][0]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][0]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][0]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][0]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][0]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$farr[$i]["kemu"][0]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$farr[$i]["kemu"][0]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$farr[$i]["kemu"][0]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$farr[$i]["kemu"][0]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$farr[$i]["kemu"][0]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$farr[$i]["kemu"][0]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$farr[$i]["kemu"][0]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
            $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$farr[$i]["kemu"][0]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            for($j=1;$j<count($kemu);$j++){
                $h++;
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('B'.$h,$farr[$i]["kemu"][$j]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('C'.$h,$farr[$i]["kemu"][$j]["jine"][0],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('D'.$h,$farr[$i]["kemu"][$j]["jine"][1],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('E'.$h,$farr[$i]["kemu"][$j]["jine"][2],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('F'.$h,$farr[$i]["kemu"][$j]["jine"][3],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('G'.$h,$farr[$i]["kemu"][$j]["jine"][4],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('H'.$h,$farr[$i]["kemu"][$j]["jine"][5],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('I'.$h,$farr[$i]["kemu"][$j]["jine"][6],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('J'.$h,$farr[$i]["kemu"][$j]["jine"][7],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('K'.$h,$farr[$i]["kemu"][$j]["jine"][8],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('L'.$h,$farr[$i]["kemu"][$j]["jine"][9],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('M'.$h,$farr[$i]["kemu"][$j]["jine"][10],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('N'.$h,$farr[$i]["kemu"][$j]["jine"][11],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('O'.$h,$farr[$i]["kemu"][$j]["avg"],PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->setCellValueExplicit('P'.$h,$farr[$i]["kemu"][$j]["zjine"],PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }
         $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
            $objPHPExcel->getActiveSheet()->getStyle('A1:P'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        //设置宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
            $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
           
        //var_dump($objWriter);exit;
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=file.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');  
    }
    public function propotionpie($kid,$cid,$fid){
        $sheng=db("cg_net")->where("id=$fid")->value("title");
        $this->assign("sheng",$sheng);
        $cainian=db("cg_cainian")->where(["id"=>$cid])->find();
        $kaohe2=db("cg_kaoheqi1 k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->where("k1.cid={$cid}")->order("k2.starttime")
        ->field("k1.id as k1id,k1.starttime as k1start,k1.endtime as k1end,k2.id,k2.title,k2.starttime,k2.endtime")->select();
        $this->assign("cainian",$cainian);
         $karr=explode('-',$kid);
        $kid=implode(',',$karr);
        $kemu=db("cg_feiyongkemu3")->where("id in ($kid)")->field("id,title")->select();
        $where="t.status=7 and (t.status2 is null or t.status2=0) ";
        $key=0;
        for($i=0;$i<count($kaohe2);$i++){
           
            $start=$kaohe2[$i]["starttime"];
            $end=$kaohe2[$i]["endtime"]+3600*24;
            $kaohe2[$i]["zonge"]=0;
            $kaohe2[$i]["kemu"]=$kemu;
            for($j=0;$j<count($kemu);$j++){
                $kaohe2[$i]["kemu"][$j]["jine"]=0;
                $tmpjine=db("cg_ti t")->join("cg_ti_list td","td.ti_id=t.id")->join("cg_jxs j","j.id=t.jxs_id")
                ->join("cg_net n","td.net_id=n.id")->join("cg_system s","s.id=n.system_id")
                    ->where($where."and n.id={$fid} and td.feetype={$kemu[$j]["id"]} and td.start>=$start and td.start<$end")->field("yjsjtrfy,fb_shijitourufeiyong")->select();
                 foreach ($tmpjine as $k => $v) {
                        # code...
                        if($v["fb_shijitourufeiyong"]){
                            $kaohe2[$i]["zonge"]+=round($v["fb_shijitourufeiyong"]);
                            $kaohe2[$i]["kemu"][$j]["jine"]+=round($v["fb_shijitourufeiyong"]);
                        }else{
                            $kaohe2[$i]["zonge"]+=round($v["yjsjtrfy"]);
                            $kaohe2[$i]["kemu"][$j]["jine"]+=round($v["yjsjtrfy"]);
                        }
                    }    
            }
            if($kaohe2[$i]["zonge"]>0){
                $kaohe2[$i]["key"]=$key; $key++;
            }
        }
        $this->assign("kaohe2",$kaohe2);
        return $this->fetch();
    }
}